package com.dliyun.db.migration;

import com.dliyun.db.migration.file.ChecksumCalculator;
import com.dliyun.db.migration.file.MigrationVersion;
import com.dliyun.db.migration.runner.IRunner;
import com.dliyun.db.migration.runner.ScriptRunner;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.core.Ordered;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;

import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Arrays;
import java.util.Comparator;
import java.util.List;

@Slf4j
public class DbMigration implements InitializingBean, Ordered {
	private final DataSource dataSource;

	public DbMigration(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	@Override
	public void afterPropertiesSet() throws Exception {
		log.info("DB Migration Starting...");

		try {
			ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();

			boolean r1 = this.execute(dataSource, conn -> this.executeFileScript(conn, resolver.getResource("db_migration_schema_history.sql")));
			if (!r1) {
				throw new Exception("DbMigration init fail");
			}

			Resource[] resources = resolver.getResources("db/migration/*.sql");
			List<Resource> listResource = Arrays.asList(resources);
			listResource.sort(new Comparator<Resource>() {
				@Override
				public int compare(Resource o1, Resource o2) {
					if (o1.getFilename() != null && o2.getFilename() != null) {
						return o1.getFilename().compareTo(o2.getFilename());
					} else {
						return 0;
					}
				}
			});
			for (int index = 0; index < resources.length; index++) {
				final int installId = index + 1;

				Resource resource = resources[index];
				String fileName = resource.getFilename();
				if (fileName == null || "".equals(fileName)) {
					throw new Exception(String.format("%s read fail", fileName));
				}

				int checksum = ChecksumCalculator.calculate(resource);
				log.debug("[{}] checksum is : {}", fileName, checksum);
				MigrationVersion migrationVersion = MigrationVersion.fromVersion(fileName.substring(1, fileName.indexOf("__")));
				Connection connection = dataSource.getConnection();
				DatabaseMetaData metaData = connection.getMetaData();
				String installedBy = metaData.getUserName();

				String querySql = "select `f_version`, `f_name`, `checksum`,`success` from `db_migration_history` where `id`=?";
				PreparedStatement queryPs = connection.prepareStatement(querySql);
				queryPs.setInt(1, installId);
				ResultSet rs = queryPs.executeQuery();
				Integer dbChecksum = null;
				if (rs.next()) {
					dbChecksum = rs.getInt("checksum");

					String fVersion = rs.getString("f_version");
					String fName = rs.getString("f_name");

					if (MigrationVersion.fromVersion(fVersion).compareTo(migrationVersion) != 0 || !fileName.equals(fName)) {
						throw new Exception(String.format("[%s]与已经执行的版本[%s]不匹配", fileName, fName));
					}
					boolean success = rs.getBoolean("success");
					if (!success) {
						throw new Exception(String.format("[%s]曾经执行失败，请修正并删除执行记录后再重新执行后再重新执行！", fileName));
					}
				}
				connection.close();

				if (dbChecksum == null) {
					boolean r2 = this.execute(dataSource, conn -> {
						String installSql = "insert into `db_migration_history`(`id`, `f_version`, `f_name`, `checksum`, `installed_by`, `installed_on`, `execution_time`, `success`) values(?, ?, ?, ?, ?, current_timestamp(), ?, ?)";
						PreparedStatement installPs = conn.prepareStatement(installSql);
						installPs.setInt(1, installId);
						installPs.setString(2, migrationVersion.getVersion());
						installPs.setString(3, fileName);
						installPs.setInt(4, checksum);
						installPs.setString(5, installedBy);
						installPs.setLong(6, 0);
						installPs.setBoolean(7, false);
						installPs.executeUpdate();
					});
					if (r2) {
						log.info("start install sql Script : {} ", fileName);

						boolean r3 = this.execute(dataSource, conn -> {
							long startExecutionTime = new java.util.Date().getTime();
							this.executeFileScript(conn, resource);
							long endExecutionTime = new java.util.Date().getTime();

							String updateSql = "update `db_migration_history` set `execution_time`=?, `success`=? where `f_version`=?";
							PreparedStatement updatePs = conn.prepareStatement(updateSql);
							updatePs.setLong(1, endExecutionTime - startExecutionTime);
							updatePs.setBoolean(2, true);
							updatePs.setString(3, migrationVersion.getVersion());
							updatePs.executeUpdate();
						});
						log.info("install sql Script : {} >>>> {}", fileName, r3);

					} else {
						throw new Exception(String.format("install sql Script fail [%s]", fileName));
					}
				} else if (dbChecksum != checksum) {
					log.info("dbChecksum:{}>>>>>>>>>checksum:{}", dbChecksum, checksum);
					throw new Exception(String.format("[%s]被篡改，已经执行过的SQL禁止修改！", fileName));
				} else {
					log.info("[{}] already installed success", fileName);
				}
			}
		} catch (Exception e) {
			log.error("", e);
			System.exit(0);
			throw e;
		} finally {
			log.info("DB Migration Completed");
		}
	}

	@Override
	public int getOrder() {
		return 1;
	}

	private boolean execute(DataSource dataSource, IRunner runner) {
		boolean result = false;
		try {
			Connection conn = dataSource.getConnection();
			try {
				conn.setAutoCommit(false);
				runner.execute(conn);
				conn.commit();
				result = true;
			} catch (Exception e) {
				conn.rollback();
				throw e;
			} finally {
				conn.close();
			}
		} catch (Exception e) {
			log.error("", e);
		}

		return result;
	}

	private void executeFileScript(Connection connection, Resource resource) throws IOException {
		InputStream in = resource.getInputStream();
		InputStreamReader reader = new InputStreamReader(in);
		ScriptRunner scriptRunner = new ScriptRunner(connection);
		scriptRunner.setAutoCommit(false);
		scriptRunner.setStopOnError(true);
		scriptRunner.runScript(reader);
	}
}
